package osmData

import (
	"context"
	"database/sql"
	"errors"
	"log"
	"task00/pkg/db"
	"task00/pkg/domain/osmData"
	interfaces "task00/pkg/repo/account/interface"
)

type osmDataBase struct {
	db *sql.DB
}

func NewAccountOsmDataBase(db *sql.DB) interfaces.AccountRepository {
	return &osmDataBase{
		db: db,
	}
}

func (r *osmDataBase) Migrate(ctx context.Context) error {
	regionQuery := `
    CREATE TABLE IF NOT EXISTS regionOsm(
       	id SERIAL PRIMARY KEY,
		account_id INTEGER NOT NULL,
		name TEXT NOT NULL,
		parent_region TEXT,
		latitude FLOAT8 NOT NULL,
		longitude FLOAT8 NOT NULL,
		region_type INTEGER[]
    );
    `
	_, err := r.db.ExecContext(ctx, regionQuery)
	if err != nil {
		message := db.ErrMigrate.Error() + " region"
		log.Printf("%q: %s\n", message, err.Error())
		return db.ErrMigrate
	}

	osmDataQuery := `
    CREATE TABLE IF NOT EXISTS osm_data(
       	id SERIAL PRIMARY KEY,
		map_link TEXT NOT NULL,
		bounding_box FLOAT8[]
    );
    `
	_, err = r.db.ExecContext(ctx, osmDataQuery)
	if err != nil {
		message := db.ErrMigrate.Error() + " osm_data"
		log.Printf("%q: %s\n", message, err.Error())
		return db.ErrMigrate
	}

	pointOfInterestQuery := `
    CREATE TABLE IF NOT EXISTS point_of_interest(
       	id SERIAL PRIMARY KEY,
		name TEXT NOT NULL,
		type TEXT NOT NULL,
		latitude FLOAT8 NOT NULL,
		longitude FLOAT8 NOT NULL,
		osm_data_id INTEGER NOT NULL REFERENCES osm_data(id)
    );
    `
	_, err = r.db.ExecContext(ctx, pointOfInterestQuery)
	if err != nil {
		message := db.ErrMigrate.Error() + " point_of_interest"
		log.Printf("%q: %s\n", message, err.Error())
		return db.ErrMigrate
	}

	return nil
}
func (r *osmDataBase) POST(ctx context.Context, region *osmData.Region) (*osmData.Region, error) {
	_, err := r.db.ExecContext(ctx, `
        INSERT INTO regionOsm (
            name, 
            parent_region, 
            latitude, 
            longitude, 
            osm_data
        ) VALUES (
            $1, 
            $2, 
            $3, 
            $4, 
            $5
        ) RETURNING *`,
		region.Name,
		region.ParentRegion,
		region.Latitude,
		region.Longitude,
		region.OsmData)
	if err != nil {
		return nil, err
	}

	return region, nil
}

func (r *osmDataBase) Put(ctx context.Context, id int64, region *osmData.Region) (*osmData.Region, error) {
	_, err := r.db.ExecContext(ctx, `
        UPDATE regionOsm SET 
            name = $2, 
            parent_region = $3, 
            latitude = $4, 
            longitude = $5, 
            osm_data = $6 
        WHERE id = $1`,
		id,
		region.Name,
		region.ParentRegion,
		region.Latitude,
		region.Longitude,
		region.OsmData)
	if err != nil {
		return nil, err
	}

	return region, nil
}

func (r *osmDataBase) Get(ctx context.Context, id int64) (*osmData.Region, error) {
	row := r.db.QueryRowContext(ctx, "SELECT * FROM regionOsm WHERE id = $1", id)

	var result osmData.Region
	err := row.Scan(&result.ID, &result.AccountID, &result.Name, &result.ParentRegion, &result.Latitude, &result.Longitude, &result.RegionType, &result.OsmData.MapLink, &result.OsmData.BoundingBox, &result.OsmData.PointsOfInterest)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, db.ErrNotExist
		}
		return nil, err
	}

	return &result, nil
}

func (r *osmDataBase) Delete(ctx context.Context, id int64) error {
	res, err := r.db.ExecContext(ctx, "DELETE FROM regionOsm WHERE id = $1", id)
	if err != nil {
		return err
	}

	rowsAffected, err := res.RowsAffected()
	if err != nil {
		return err
	}

	if rowsAffected == 0 {
		return db.ErrDeleteFailed
	}

	return err
}
